﻿clear all
set more off

* Load macroeconomic data
clear
input str10 date sticky_cpi import_price export_price gdp
"2023-01-01" 6.488 141.3 151.1 27164.359
"2023-04-01" 6.267 140.3 151.0 27453.815
"2023-07-01" 5.402 139.4 147.5 27967.697
"2023-10-01" 4.882 140.1 148.6 28296.967
"2024-01-01" 4.601 139.4 147.5 28624.069
"2024-04-01" 4.403 141.7 149.8 29016.714
"2024-07-01" 4.179 141.8 149.2 29374.914
"2024-10-01" 3.963 141.1 148.8 29719.647
end

* Convert date to Stata format (Quarterly)
gen date2 = date(date, "YMD")
format date2 %td
drop date
rename date2 date
gen quarter = quarter(date)
gen year = year(date)
gen qdate = yq(year, quarter)
format qdate %tq
drop year quarter
rename qdate date

* Set time series format
tsset date, quarterly

* Generate macroeconomic variables
gen gdp_growth = (gdp - L.gdp) / L.gdp
gen trade_policy_uncertainty = abs(import_price - export_price)

* Save macroeconomic data
save "macro_data.dta", replace


clear
input str10 date price_F price_UNH market_index
"2023-01-01" 15.2 520.1 4000
"2023-02-01" 15.6 522.3 4020
"2023-03-01" 16.0 521.5 4035
"2023-04-01" 15.8 519.8 4015
"2023-07-01" 14.9 516.8 3965
"2023-10-01" 14.2 512.5 3925
"2024-01-01" 13.5 508.0 3890
"2024-04-01" 12.8 504.0 3865
"2024-07-01" 12.3 502.5 3850
"2024-10-01" 12.0 500.0 3835
end

* Convert date to Stata format (Monthly)
gen date2 = date(date, "YMD")
format date2 %td
drop date
rename date2 date
gen month = month(date)
gen year = year(date)
gen mdate = ym(year, month)
format mdate %tm
drop year month
rename mdate date

* Set time series format
tsset date, monthly

* Compute firm-level stock returns
gen ret_F = (price_F - L.price_F) / L.price_F
gen ret_UNH = (price_UNH - L.price_UNH) / L.price_UNH
gen market_return = (market_index - L.market_index) / L.market_index

* Save firm-level stock data
save "firm_data.dta", replace


clear
input str10 date total_revenue_F cost_revenue_F operating_income_F net_income_F eps_F total_revenue_UNH cost_revenue_UNH operating_income_UNH net_income_UNH eps_UNH
"2023-12-31" 45962 43432 -245 -526 . 94427 72425 7689 5455 5.90
"2024-03-31" 42777 39176 1225 1332 0.33 99796 76791 7931 -1409 -1.53
"2024-06-30" 47808 43247 1883 1831 0.46 98855 76798 7875 4216 4.58
"2024-09-30" 46196 42860 880 892 0.22 100820 77791 8708 6055 6.51
"2024-12-31" 48211 44203 1231 1824 . 100807 79499 7773 5543 6.06
end

* Convert date to Stata format (Quarterly)
gen date2 = date(date, "YMD")
format date2 %td
drop date
rename date2 date
gen quarter = quarter(date)
gen year = year(date)
gen qdate = yq(year, quarter)
format qdate %tq
drop year quarter
rename qdate date

* Set time series format
tsset date, quarterly

* Compute profitability and efficiency measures
gen op_margin_F = operating_income_F / total_revenue_F
gen op_margin_UNH = operating_income_UNH / total_revenue_UNH
gen profit_margin_F = net_income_F / total_revenue_F
gen profit_margin_UNH = net_income_UNH / total_revenue_UNH

* Save financial metrics data
save "financials.dta", replace


use "firm_data.dta", clear
merge 1:1 date using "macro_data.dta", nogen
merge 1:1 date using "financials.dta", nogen
save "final_data.dta", replace

summarize gdp gdp_growth trade_policy_uncertainty price_F price_UNH market_index ret_F ret_UNH market_return op_margin_F op_margin_UNH profit_margin_F profit_margin_UNH

* Ensure the dataset is sorted before generating lagged values
sort date 

* Generate stock returns correctly
gen ret_F = (price_F - L.price_F) / L.price_F if L.price_F != .
gen ret_UNH = (price_UNH - L.price_UNH) / L.price_UNH if L.price_UNH != .
gen market_return = (market_index - L.market_index) / L.market_index if L.market_index != .

* Convert date format for better readability in graphs
format date %tmMonCCYY

* Graph: GDP & Trade Policy Uncertainty Over Time
graph twoway (line gdp date, sort lcolor(blue)) (line trade_policy_uncertainty date, sort lcolor(red)), title("GDP & Trade Policy Uncertainty Over Time") legend(order(1 "GDP" 2 "Trade Policy Uncertainty"))

* Graph: Ford vs. UnitedHealth Stock Prices with Dual Y-Axis
twoway (line price_F date, sort lcolor(blue) yaxis(1)) (line price_UNH date, sort lcolor(green) yaxis(2)), title("Stock Prices: Ford (Left) & UnitedHealth (Right)") ytitle("Ford Stock Price", axis(1)) ytitle("UNH Stock Price", axis(2)) legend(order(1 "Ford (F)" 2 "UNH"))

* Graph: Market Index vs. Stock Prices
graph twoway (line market_index date, sort lcolor(black)) (line price_F date, sort lcolor(blue)) (line price_UNH date, sort lcolor(green)), title("Market Index & Stock Prices") legend(order(1 "Market Index" 2 "Ford (F)" 3 "UnitedHealth (UNH)"))
